package com.cx.bank.util;

import com.cx.bank.model.UserBean;
import com.mysql.cj.protocol.Resultset;

import java.sql.*;

/**
 * @description:改变原有的文件存储数据为数据库存储数据
 * @author:gong
 * @create: 14/7/2022 下午6:36
 */
public class MysqlConnect {
    Connection con; // 声明Connection对象
    public static String user;
    public static  String password;
    public Connection getConnection() { // 建立返回值为Connection的方法
        try { // 加载数据库驱动类
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("数据库驱动加载成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        user = "root";//数据库登录名
        password = "123456";//密码
        try { // 通过访问数据库的URL获取数据库连接对象
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank?useUnicode=true&characterEncoding=gbk", user, password);
            System.out.println("数据库连接成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con; // 按方法要求返回一个Connection对象
    }
    public int InsertUser(String username,String password){
        MysqlConnect connect = new MysqlConnect();
        int result = 0;
        try {
            String sql = "insert into user values(?,?,?,?)";
            PreparedStatement ps = connect.getConnection().prepareStatement(sql);
            ps.setString(1,username);
            ps.setString(2,password);
            ps.setDouble(3,0);
            ps.setInt(4,0);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public int addMoney(UserBean user,double money1){
        MysqlConnect connect = new MysqlConnect();
        String username = user.getUsername();
        double money = user.getMoney();
        double realmoney = money+money1;
        int admin = user.getAdmin();
        int result = 0;
        try {
            String sql = "update user set money=? where username=?";
            PreparedStatement ps = connect.getConnection().prepareStatement(sql);
            ps.setDouble(1,realmoney);
            ps.setString(2,username);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;

    }

    public int reduceMoney(UserBean user,double money1){
        MysqlConnect connect = new MysqlConnect();
        String username = user.getUsername();
        double money = user.getMoney();
        double realmoney = money-money1;
        if (realmoney<0){
            System.out.println("余额不足");
        }
        int result = 0;
        try {
            String sql = "update user set money=? where username=?";
            PreparedStatement ps = connect.getConnection().prepareStatement(sql);
            ps.setDouble(1,realmoney);
            ps.setString(2,username);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
    public ResultSet selectAllUser(){
        MysqlConnect connect = new MysqlConnect();
        ResultSet result = null;
        try {
            Statement statement = connect.getConnection().createStatement();
            String sql = "select * from user";
            result = statement.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;

    }

    public void deleteUser(String username){
        MysqlConnect connect = new MysqlConnect();

        String sql = "delete from user where username=?";
        try {
            PreparedStatement ps = connect.getConnection().prepareStatement(sql);
            ps.setString(1,username);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public UserBean selectUserByName(String username){
        MysqlConnect connect = new MysqlConnect();
        String sql = "select * from user where username=?";
        UserBean user = new UserBean();
        try {
            PreparedStatement ps = connect.getConnection().prepareStatement(sql);
            ps.setString(1,username);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()){
                user.setUsername((String) resultSet.getObject(1));
                user.setPassword((String) resultSet.getObject(2));
                user.setMoney((double)resultSet.getObject(3));
                user.setAdmin(Integer.parseInt(resultSet.getObject(4).toString()));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    public int updateAdmin(UserBean user,int admin){
        MysqlConnect connect = new MysqlConnect();
        String username = user.getUsername();
        int result = 0;
        try {
            String sql = "update user set admin=? where username=?";
            PreparedStatement ps = connect.getConnection().prepareStatement(sql);
            ps.setDouble(1,admin);
            ps.setString(2,username);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
    public static void main(String[] args) { // 主方法，测试连接
        MysqlConnect c = new MysqlConnect(); // 创建本类对象
        c.getConnection(); // 调用连接数据库的方法
//        c.InsertUser(new UserBean("gong","123",100,1));
//        UserBean user = c.selectUserByName("gong");
//        c.reduceMoney(user,50);

    }
}
